Blog 09/07/2021

Integratie van Word met de Oracle database

In de vele reclamebrieven die je ontvangt zie je dat je eigen naam en adresgegevens zijn gebruikt, de rest van de inhoud is vaak standaard. Is dit ook mogelijk vanuit Oracle en hoe? Er zijn diverse manieren om de Oracle database en Word te koppelen.
Stel je hebt een database vol met contactpersonen en hun adresgegevens. Die gegevens wil je invoegen in een gepersonaliseerd document, zoals een uitnodiging of een commerciële brief. De meest voor de hand liggende aanpak is dan gebruik te maken van Oracle BI Publisher of een andere commerciële rapportage-oplossing.

Als je geen extra product wil aanschaffen, zit je al snel vast aan het gebruik van handmatige ODBC-koppelingen in het Word-document zelf. Voor elk document kan je dan in Word een template (het basisdocument met de algemene tekst) maken. In ieder template kan je dan via ODBC een koppeling maken naar de database. Het kiezen van de juiste data in combinatie met het juiste template vraagt om zorgvuldigheid. Elke aanpassing aan de gegevens die in het Word-document moet worden getoond, is erg bewerkelijk.

Dit blog beschrijft een andere manier om gegevens uit een database samen te voegen in een Word-document. Deze methode is minder complex, makkelijk te onderhouden en eenvoudig te implementeren. We gebruiken hiervoor de Oracle PL/SQL Utility Library “Alexandria”.

Michel Vogelpoel /

Integratie expert

Opzet

Voor dit blog maken we een online applicatie waarmee je een persoon kan selecteren uit de Oracle database en daarna een Word-template kan kiezen. Met een druk op de knop worden de gegevens van die persoon in het Word-document getoond.

De applicatie maakt het eenvoudig om nieuwe Word-templates (bijv. brieven) toe te voegen en aan te passen.

Wat we nodig hebben voor deze applicatie:

Word-document template in docx formaat (Microsoft Word versie 2007 en hoger)
Oracle packages met tools voor unzippen / editen van Word-documenten
Oracle Application Express (APEX) (vanaf versie 4)
Client machine, waarop de browser op draait, moet Word hebben geïnstalleerd.

Word-template

Voor deze case gaan we uit van een simpel Word-template. Het template ziet er bijvoorbeeld als volgt uit:

Om aan te geven waar we tekst uit de database willen neerzetten, gebruiken we zogenaamde “tags” in het document. In dit geval gebruiken we tags die beginnen en eindigen met een #.

In het eindresultaat willen we dat deze tags vervangen zijn met waarden uit de Oracle-database zodat de correcte aanhef en adresgegevens zichtbaar worden in het Word-document.

De template mag plaatjes bevatten en mag verder elke opmaak (lettertype, etc.) hebben die gewenst is. Let wel op dat je de tags (bijv. #POSTCODE_MWR# ) in één keer correct intypt en dus niet later een deel van de tag aanpast. De kans is dan groot dat binnen het Word-document, in de XML op de achtergrond, de tag niet meer als één geheel wordt gezien. Een goede manier is de tag te kopiëren naar Notepad, aan te passen, te kopiëren en dan weer te plakken in het Word-document.

Oracle-packages

Het docx formaat dat World sinds versie 2007 kent, is een gezipt XML-document. Als het is uitgepakt, kan het worden benaderd via Oracle XML.

Dit is nu mogelijk door de PLSQL-utils, Oracle PL/SQL Utility Library, codename "Alexandria".

Op site https://github.com/mortenbra/alexandria-plsql-utils staat een zip file met daarin de benodigde Oracle-packages om dit mogelijk te maken.

De volgende packages uit de zip file zijn nodig voor wat we hier willen. Installeer deze packages in je werkomgeving:

- ooxml_util_pkg.pks
- ooxml_util_pkg.pkb
- sql_util_pkg.pks
- sql_util_pkg.pkb
- string_util_pkg.pks
- string_util_pkg.pkb
- xml_stylesheet_pkg.pks
- xml_stylesheet_pkg.pkb
- xml_util_pkg.pks
- xml_util_pkg.pkb
- zip_util_pkg.pks
- zip_util_pkg.pkb

Tevens zijn de volgende types nodig, installeer deze in je werkomgeving:

CREATE OR REPLACE TYPE "T_STR_ARRAY" AS TABLE OF VARCHAR2(4000);
CREATE TYPE T_NUM_ARRAY AS TABLE OF NUMBER;

APEX

De gemakkelijkste manier om dit alles aan elkaar vast te knopen is via APEX. Hiervoor hebben we de Word-template als BLOB nodig in de database. We moeten eerst het uploaden van de Word-template naar de database mogelijk maken, waarvoor we een upload templatepagina maken.

Een hele goede beschrijving hiervoor is te vinden op het blog van Ittichai Chammavanijakul (https://ittichaicham.com/2011/03/file-browser-in-apex-4-with-blob-column-specified-in-item-source-attribute/)

Maak een tweede APEX-pagina met daarin:
-Een selectie LOV naar de medewerker tabel
-Een selectie LOV naar de tabel waar de templates als BLOBS opgeslagen zijn
- Een knop om de generatie te starten
- Maak een branch terug naar de huidige pagina, met in Request de waarde: “GENEREER_DOC”
- Maak een proces "on-load before header" . In Condition type zet “Request = Expression 1” en in Expression 1 de waarde “GENEREER_DOC”

De combinatie van het kiezen van een medewerker en het kiezen van de template op het scherm zorgt voor veel flexibiliteit. Het proces heeft de volgende code:

DECLARE
  l_new_file blob;
  l_doc_id NUMBER;
  l_names  T_STR_ARRAY := T_STR_ARRAY();
  l_values T_STR_ARRAY := T_STR_ARRAY();
  l_teller NUMBER;
  l_record_nr NUMBER;
  v_mime  VARCHAR2(100) := 'application/vnd.openxmlformats-officedocument.wordprocessingml.document';
  v_length  NUMBER;
  l_new_file_name VARCHAR2(200);
  -- De WORD templates in de database
  CURSOR c_template IS
     SELECT dte_filename
          , dte_data
       FROM document_templates
      WHERE dte_id = :P1_SELECT_TEMPLATE;
  r_template c_template%ROWTYPE;
 
  -- De data uit de database welke we nodig hebben op de tags te vervangen
  CURSOR c_medewerker  IS
    SELECT naam
         , adres
         , postcode
         , woonplaats
     FROM medewerker
    WHERE mdr_id = :P1_MDR_ID;
   r_medewerker c_medewerker%ROWTYPE;

  FUNCTION replace_special_chars ( l_text VARCHAR2 )  RETURN VARCHAR2 IS
     l_return  VARCHAR2(4000);
  BEGIN
     l_return := l_text;
     l_return := SUBSTR(REPLACE ( l_return, '&', '&amp;'), 1, 4000);
     l_return := SUBSTR(REPLACE ( l_return, '<', '&lt;'), 1, 4000);
     l_return := SUBSTR(REPLACE ( l_return, '>', '&gt;'), 1, 4000);
     l_return := SUBSTR(REPLACE ( l_return, chr(10), '<w:br/>'), 1, 4000);

     RETURN l_return;
  END;
BEGIN
   OPEN c_template;
   FETCH c_template INTO r_template;
   IF c_template%NOTFOUND
   THEN
      Wwv_flow.debug('NOT FOUND');
        raise_application_error ( -20000, 'kan template niet vinden');
        null;
   END IF;
   CLOSE c_template;

   -- Genereer een ID voor het nieuw te maken document
   SELECT "DOCUMENTEN_SEQ".nextval
   INTO l_doc_id
   FROM dual;

   -- Maak arrays met de tag name en tag inhoud
   l_teller := 1;
   OPEN c _medewerker;
   FETCH c_medewerker into r_medewerker;
   CLOSE c_medewerker;
   
   IF r_medewerker IS NOT NULL
   THEN
      -- Hier vullen we een array met de tags, en met welke waarde die vervangen moet worden
      l_names.EXTEND(4);
      l_values.EXTEND(4);
      
      l_names(1) := '#NAAM#';
      l_names(2) := '#ADRES#';
      l_names(3) := '#POSTCODE#';
      l_names(4) := '#WOONPLAATS#';
      
      l_value(1) := r_medewerker.naam;
      l_value(2) := r_medewerker.adres;
      l_value(3) := r_medewerker.postcode;
      l_value(4) := r_medewerker.woonplaats;
      
      -- Het aanpassen van de template document, hier wordt de replace gedaan van de tags naar de Oracle waarden
      l_new_file := ooxml_util_pkg.get_file_from_template ( r_template.dte_data, l_names, l_values);
   
      -- maken van de nieuwe file
      l_new_file_name := 'Uitnodiging_'||l_doc_id||'.docx';
   
      v_length := DBMS_LOB.GETLENGTH(l_new_file);
      owa_util.mime_header( v_mime, FALSE );
      htp.p('Content-length: ' || v_length);
      htp.p('Content-Disposition:  attachment; filename="'||l_new_file_name||'"');
      owa_util.http_header_close;
 
      wpg_docload.download_file( l_new_file );
   END IF;  
END;

Resultaat

De code "wpg_docload.download_file( l_new_file );" zorgt ervoor dat de aangepaste Word-template direct wordt geopend op het scherm in Word. De tags zijn dan reeds vervangen met de Oracle-waarden. De gebruiker kan nu kiezen voor het direct printen of het document opslaan.

Natuurlijk zijn er vele andere mogelijkheden voor het vullen van de arrays die de tags en te vervangen waardes (l_names en l_values) bevatten. De hiervoor benodigde data kan ook bijvoorbeeld in een tabel staan, of uit een view worden gehaald. Een voordeel daarvan is dat het onderhoud aan de bovenstaande procedure dan geminimaliseerd kan worden.

De functie "ooxml_util_pkg.get_file_from_template" werkt zowel voor Word (docx), Excel (xlsx), of Powerpoint (pptx). Tevens bevat de ooxml_util_pkg packags diverse mogelijkheden om waardes uit specifieke cellen van een Excel document op te halen.

Conclusie

Wat ik hier heb laten zien, is dat met minimale aanpassingen van een Word-template, in combinatie met een eenvoudige APEX-applicatie en de kant en klare Oracle-packages, integratie van Office met de Oracle-database voor iedere applicatie tot de mogelijkheden behoort. Een "proof of concept" kan binnen een dag opgeleverd worden.
De hier getoonde opzet is niet echt geschikt voor het automatisch genereren van bulk documenten, maar meer voor het op ad-hoc basis maken van een specifieke brief voor een persoon. Voor een meer geautomatiseerd proces zou Oracle BI Publisher of Oracle Reports meer geschikt zijn.